package DeviceData 
{
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	
	/**
	 * @author Simon Claeye
	 * http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLConnection.html
	 * 
	 * http://help.adobe.com/en_US/as3/dev/WS5b3ccc516d4fbf351e63e3d118666ade46-7d49.html
	 */
	public class LocalDB 
	{
		private var sqlConnectionSync:SQLConnection;
		
		//SQL statements
		//User
		private var insertUserToken:SQLStatement;
		private var getUserToken:SQLStatement;
		private var deleteUserTokens:SQLStatement;
		
		//Image
		private var insertImage:SQLStatement;
		private var getImages:SQLStatement;
		private var updateImage:SQLStatement;
		private var deleteImage:SQLStatement;
		
		//Tag
		private var insertTag:SQLStatement;
		private var getTags:SQLStatement;
		private var deleteTag:SQLStatement;   
		private var getTag:SQLStatement;

		public function LocalDB() 
		{
			//create the database file in the application storage folder
			var file:File = File.applicationStorageDirectory.resolvePath("GeologiseData.db");
			sqlConnectionSync = new SQLConnection();
			sqlConnectionSync.open(file, SQLMode.CREATE);
				
			//Create the tables
			var createImages:SQLStatement = new SQLStatement();
			createImages.text = "CREATE TABLE IF NOT EXISTS images (native_path VARCHAR(256) UNIQUE, "+
			"latitude DECIMAL, longitude DECIMAL, altitude DECIMAL, hAccuracy DECIMAL, vAccuracy DECIMAL, heading DECIMAL, notes VARCHAR(512))";
			createImages.sqlConnection = sqlConnectionSync;
			createImages.execute();
			
			var createTags:SQLStatement = new SQLStatement();
			createTags.text = "CREATE TABLE IF NOT EXISTS tags (native_path VARCHAR(256), tag VARCHAR(64))";
			createTags.sqlConnection = sqlConnectionSync;
			createTags.execute();
			
			var createUser:SQLStatement = new SQLStatement();
			createUser.text = "CREATE TABLE IF NOT EXISTS user (token VARCHAR(41))";		//Max of 40 chars on the server, using 41 just to safe
			createUser.sqlConnection = sqlConnectionSync;
			createUser.execute();
			
			//Set up the SQL prepared statements
			
			//User
			insertUserToken = new SQLStatement();
			insertUserToken.sqlConnection = sqlConnectionSync;
			insertUserToken.text = "INSERT INTO user (token) VALUES (?)";
			
			getUserToken = new SQLStatement();
			getUserToken.sqlConnection = sqlConnectionSync;
			getUserToken.text = "SELECT * FROM user";
			
			deleteUserTokens = new SQLStatement();
			deleteUserTokens.sqlConnection = sqlConnectionSync;
			deleteUserTokens.text = "DELETE FROM user";
			
			//Image
			insertImage = new SQLStatement();
			insertImage.sqlConnection = sqlConnectionSync;
			insertImage.text = "INSERT INTO images (native_path, latitude, longitude, altitude, hAccuracy, "+
			"vAccuracy, heading, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
			
			getImages = new SQLStatement();
			getImages.sqlConnection = sqlConnectionSync;
			getImages.text = "SELECT * FROM images";
			
			updateImage = new SQLStatement();
			updateImage.sqlConnection = sqlConnectionSync;
			updateImage.text = "UPDATE images SET notes = ? WHERE images.native_path = ?";
			
			deleteImage = new SQLStatement();
			deleteImage.sqlConnection = sqlConnectionSync;
			deleteImage.text = "DELETE FROM images WHERE images.native_path = ?";
			
			//Tag
			insertTag = new SQLStatement();
			insertTag.sqlConnection = sqlConnectionSync;
			insertTag.text = "INSERT INTO tags (native_path, tag) VALUES (?, ?)";
			
			getTags = new SQLStatement();
			getTags.sqlConnection = sqlConnectionSync;
			getTags.text = "SELECT * FROM tags";
			
			getTag = new SQLStatement();
			getTag.sqlConnection = sqlConnectionSync;
			getTag.text = "SELECT * FROM tags WHERE tags.native_path = ? ";
			
			deleteTag = new SQLStatement();
			deleteTag.sqlConnection = sqlConnectionSync;
			deleteTag.text = "DELETE FROM tags WHERE tags.native_path = ?";
		}
		
		/**
		 * Insert a new authentication token into the local database.
		 * @param	token
		 */
		public function addToken(token:String):void
		{
			insertUserToken.clearParameters();
			insertUserToken.parameters[0] = token;
			insertUserToken.execute();
		}
		
		/**
		 * Delete all authenication tokens.
		 */
		public function deleteTokens():void
		{
			deleteUserTokens.execute();
		}
		
		/**
		 * Retrieve the authenciation token.
		 */
		public function getToken():Array
		{
			getUserToken.execute();
			return getUserToken.getResult().data;
		}
		
		/**
		 * Add image data to the database
		 * @param	native_path the native path of the image to which the data should be linked
		 * @param	latitude 
		 * @param	longitude
		 * @param	altitude
		 * @param	horizontalAcc the gps horizontal accuracy
		 * @param	verticalAcc the gps vertical accuracy
		 * @param	heading
		 * @param	notes
		 */
		public function writeImageData(native_path:String, latitude:Number, longitude:Number, altitude:Number, horizontalAcc:Number,
		verticalAcc:Number, heading:Number, notes:String):void
		{
			insertImage.clearParameters();
			insertImage.parameters[0] = native_path;
			insertImage.parameters[1] = latitude;
			insertImage.parameters[2] = longitude;
			insertImage.parameters[3] = altitude;
			insertImage.parameters[4] = horizontalAcc;
			insertImage.parameters[5] = verticalAcc;
			insertImage.parameters[6] = heading;
			insertImage.parameters[7] = notes;
			insertImage.execute();
		}
		
		/**
		 * Funciton to get all image data.
		 * @return an array of image objects
		 */
		public function readImageData():Array
		{
			getImages.execute();
			return getImages.getResult().data
		}
		
		/**
		 * Function to update image data in the local database.
		 * @param	native_path the native path of the image for which the data should be updated
		 * @param	notes
		 */
		public function updateImageData(native_path:String, notes:String):void
		{
			updateImage.clearParameters();
			updateImage.parameters[0] = notes;
			updateImage.parameters[1] = native_path;
			updateImage.execute();
		}
		
		/**
		 * Function to delete image data in the local database.
		 * @param	native_path the native path of the image for which the data should be deleted
		 */
		public function deleteImageData(native_path:String):void
		{
			deleteImage.clearParameters();
			deleteImage.parameters[0] = native_path;
			deleteImage.execute();
		}
		
		/**
		 * Function to add tag data to the local database.
		 * @param	native_path the native path of the image to which the tags should be added.
		 * @param	tag
		 */
		public function writeTagData(native_path:String, tag:String):void
		{
			insertTag.clearParameters();
			insertTag.parameters[0] = native_path;
			insertTag.parameters[1] = tag;
			insertTag.execute();
		}
		
		/**
		 * Function to retrieve all tags for an image.
		 * @param	native_path the native path of the image for which tag data should be found
		 * @return an array of tag objects
		 */
		public function readTagData(native_path:String):Array
		{
			getTag.clearParameters();
			getTag.parameters[0] = native_path;
			getTag.execute();
			return getTag.getResult().data;
		}
		
		/**
		 * Deletes all of the tags associated with an image.
		 * @param	native_path the native path of the image for which tag data should be deleted
		 */
		public function deleteTagData(native_path:String):void
		{
			deleteTag.clearParameters();
			deleteTag.parameters[0] = native_path;
			deleteTag.execute();
		}
	}
}